package com.opennews.openplatform.service;

import com.opennews.openplatform.dto.income.expense.detailt.ype.CreateIncomeExpenseDetailTypeDto;
import com.opennews.openplatform.dto.income.expense.detailt.ype.UpdateIncomeExpenseDetailTypeDto;
import com.opennews.openplatform.entity.tables.records.IncomeExpenseDetailBaseTypeRecord;
import com.opennews.openplatform.entity.tables.records.IncomeExpenseDetailTypeRecord;
import com.opennews.openplatform.jooq.DSLContextWrapper;
import com.opennews.openplatform.jooq.RecordHelper;
import com.opennews.openplatform.myspringbootcore.util.SharedUtil;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static com.opennews.openplatform.entity.Tables.*;

@RequiredArgsConstructor
@Service
public class IncomeExpenseDetailTypeService {
    private final DSLContext dslContext;
    private final DSLContextWrapper dslContextWrapper;

    /**
     * Prepares the initial data for IncomeExpenseDetailType.
     */
    public void init(String accountGroupId) {
        int count = dslContextWrapper.count(INCOME_EXPENSE_DETAIL_TYPE, INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId));

        if (count == 0) {
            List<IncomeExpenseDetailBaseTypeRecord> baseTypes = dslContext.selectFrom(INCOME_EXPENSE_DETAIL_BASE_TYPE).fetch();
            List<IncomeExpenseDetailTypeRecord> records = new ArrayList<>();

            // Prepares the default expense types based on predefined base types.
            for (IncomeExpenseDetailBaseTypeRecord baseType : baseTypes) {
                IncomeExpenseDetailTypeRecord record = baseType.into(IncomeExpenseDetailTypeRecord.class);
                RecordHelper.setCommonFields(record);
                record.setAccountGroupId(accountGroupId);
                records.add(record);
            }

            dslContext.batchInsert(records).execute();
        }
    }

    /**
     * Queries all detail types which associated with provided account group.
     *
     * @param accountGroupId     : The string of the user account group id.
     * @param incomeExpenseType: The string of income/expense type.
     * @return List of map instance which contains keys below.
     * id: String of detail type id.
     * incomeExpenseType: String of income/expense type.
     * name: String of detail type name.
     * title: String of detail type title.
     * subtitle: String of detail type subtitle.
     * analysisIncluded: Bool to indicate if this is included for analysis.
     * dateCreated: The formatted created date.
     * lastUpdated: The formatted last updated date.
     */
    public List<Map<String, Object>> queryByAccountGroup(String accountGroupId, String incomeExpenseType) {
        var query = dslContext.select(
                        INCOME_EXPENSE_DETAIL_TYPE.ID.as("id"),
                        INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.as("incomeExpenseType"),
                        INCOME_EXPENSE_DETAIL_TYPE.NAME.as("name"),
                        INCOME_EXPENSE_DETAIL_TYPE.TITLE.as("title"),
                        INCOME_EXPENSE_DETAIL_TYPE.SUBTITLE.as("subtitle"),
                        INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.as("analysisIncluded"),
                        INCOME_EXPENSE_DETAIL_TYPE.DATE_CREATED.as("dateCreated"),
                        INCOME_EXPENSE_DETAIL_TYPE.LAST_UPDATED.as("lastUpdated")
                )
                .from(INCOME_EXPENSE_DETAIL_TYPE)
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(
                                        SharedUtil.isNullOrEmpty(incomeExpenseType) ?
                                                DSL.trueCondition() :
                                                INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType)

                                )
                )
                .orderBy(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX.asc());

        return query.fetchMaps();
    }

    /**
     * Queries all detail types which associated with provided account group.
     *
     * @param accountGroupId:    The string of the user's account group id.
     * @param incomeExpenseType: The string of income/expense type.
     * @return List of name string.
     */
    public List<String> queryNameForAnalysisByAccountGroup(String accountGroupId, String incomeExpenseType) {
        return dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                                .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue())

                )
                .orderBy(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX.asc())
                .fetch(INCOME_EXPENSE_DETAIL_TYPE.NAME);
    }

    /**
     * Queries the input name list and makes sure they belong to the specific account group.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param names             : The string list of name.
     * @param accountGroupId:   The string of the user's account group id.
     * @return The matched name list.
     */
    public List<String> queryAccountGroupDetailTypes(String incomeExpenseType, List<String> names, String accountGroupId) {
        if (!names.isEmpty()) {
            // Queries the names match the income/expense type and account group.
            return dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
                    .where(
                            INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                                    .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.in(names))

                    )
                    .fetch(INCOME_EXPENSE_DETAIL_TYPE.NAME);
        } else {
            return new ArrayList<>();
        }
    }

    /**
     * Checks if the detail type name is already existing with the same account id, income/expense type and id.
     *
     * @param accountGroupId:    The string of the account group id.
     * @param incomeExpenseType: The string of the income/expense type.
     * @param name:              The string of detail type name.
     * @return True means the input detail type name already exists. False means NOT.
     */
    public boolean checkExisting(String accountGroupId, String incomeExpenseType, String id, String name) {
        var condition = INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.equalIgnoreCase(name))
                .and(
                        SharedUtil.isNullOrEmpty(id) ?
                                DSL.trueCondition() :
                                INCOME_EXPENSE_DETAIL_TYPE.ID.ne(id)
                );

        return dslContextWrapper.count(INCOME_EXPENSE_DETAIL_TYPE, condition) > 0;
    }

    /**
     * Queries the current max order index with provided account group id and income/expense type.
     *
     * @param accountGroupId:    The string of the account group id.
     * @param incomeExpenseType: The string of the income/expense type.
     * @return The int number of current max order index.
     */
    public int queryMaxOrderIndex(String accountGroupId, String incomeExpenseType) {
        Integer maxOrderIndex = dslContext.select(DSL.max(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX))
                .from(INCOME_EXPENSE_DETAIL_TYPE)
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                )
                .fetchOne(0, Integer.class);

        return maxOrderIndex != null ? maxOrderIndex : 0;
    }

    /**
     * Queries the next max order index with provided account group id and income/expense type.
     *
     * @param accountGroupId:    The string of the account group id.
     * @param incomeExpenseType: The string of the income/expense type.
     * @return The int number of next max order index.
     */
    public int queryNextMaxOrderIndex(String accountGroupId, String incomeExpenseType) {
        return queryMaxOrderIndex(accountGroupId, incomeExpenseType) + 1;
    }

    /**
     * Queries all detail types which associated with provided account group which is not included by analysis.
     *
     * @param accountGroupId:    The string of the user account group id.
     * @param incomeExpenseType: The string of IncomeExpenseTypeConstant.
     * @return List of map instance which contains keys below.
     * name: String of detail type name.
     * title: String of detail type title.
     */
    public List<Map<String, Object>> queryAnalysisNotIncludedDetailTypes(String accountGroupId, String incomeExpenseType) {
        return dslContext.select(
                        INCOME_EXPENSE_DETAIL_TYPE.NAME,
                        INCOME_EXPENSE_DETAIL_TYPE.TITLE
                )
                .from(INCOME_EXPENSE_DETAIL_TYPE)
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                                .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isFalse())
                )
                .orderBy(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX.asc())
                .fetchMaps();
    }

    /**
     * Resets the detail types order based on the sequence of provided id list.
     *
     * @param accountGroupId: The string of user account group id.
     * @param detailTypeIds:  The list of string of detail types.
     */
    public void resetOrder(String accountGroupId, List<String> detailTypeIds) {
        // Updates each order index.
        for (int i = 0; i < detailTypeIds.size(); i++) {
            dslContext.update(INCOME_EXPENSE_DETAIL_TYPE)
                    .set(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX, i + 1)
                    .where(
                            INCOME_EXPENSE_DETAIL_TYPE.ID.eq(detailTypeIds.get(i))
                                    .and(INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId))
                    )
                    .execute();
        }
    }

    /**
     * Adds new detail type for account group.
     *
     * @param data           : The CreateIncomeExpenseDetailType which contains keys with same name as detail type properties.
     * @param accountGroupId : The string of the user account group id.
     * @return Map instance which contains keys below.
     * id: String of detail type id.
     * incomeExpenseType: String of income/expense type.
     * name: String of detail type name.
     * title: String of detail type title.
     * subtitle: String of detail type subtitle.
     * analysisIncluded: Bool to indicate if this is included for analysis.
     * dateCreated: The formatted created date.
     * lastUpdated: The formatted last updated date.
     */
    public Map<String, Object> add(@Valid CreateIncomeExpenseDetailTypeDto data, String accountGroupId) {
        Map<String, Object> savedDetailType = null;

        if (!checkExisting(accountGroupId, data.getIncomeExpenseType(), null, data.getName())) {
            // Creates new IncomeExpenseDetailType with posted data.
            IncomeExpenseDetailTypeRecord record = dslContext.newRecord(INCOME_EXPENSE_DETAIL_TYPE);
            record.from(data);
            record.setName(record.getName().toUpperCase());
            record.setAccountGroupId(accountGroupId);
            record.setOrderIndex(queryNextMaxOrderIndex(accountGroupId, record.getIncomeExpenseType()));
            record.insert();

            savedDetailType = record.intoMap();
        }

        return savedDetailType;
    }

    /**
     * Updates detail type based on provided map data and its id.
     * The map keys must match detail type property names.
     * If the map item will be ignored if its value is null.
     *
     * @param data           : The UpdateIncomeExpenseDetailType which contains keys with same name as detail type properties.
     * @param accountGroupId : The string of the user account group id.
     * @return Map instance which contains keys below.
     * id: String of detail type id.
     * incomeExpenseType: String of income/expense type.
     * name: String of detail type name.
     * title: String of detail type title.
     * subtitle: String of detail type subtitle.
     * analysisIncluded: Bool to indicate if this is included for analysis.
     * dateCreated: The formatted created date.
     * lastUpdated: The formatted last updated date.
     */
    public Map<String, Object> update(@Valid UpdateIncomeExpenseDetailTypeDto data, String accountGroupId) {
        Map<String, Object> savedDetailType = null;

        if (!checkExisting(accountGroupId, data.getIncomeExpenseType(), data.getId(), data.getName()) &&
                dslContextWrapper.count(INCOME_EXPENSE_DETAIL_TYPE, INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(INCOME_EXPENSE_DETAIL_TYPE.ID.eq(data.getId()))) == 1) {
            // Updates the detail type.
            IncomeExpenseDetailTypeRecord detailType = dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
                    .where(INCOME_EXPENSE_DETAIL_TYPE.ID.eq(data.getId()))
                    .fetchOne();

            String oldDetailType = detailType.getName();

            detailType.from(data);
            detailType.setName(detailType.getName().toUpperCase());
            detailType.update();

            String newDetailType = detailType.getName();

            dslContext.update(INCOME_EXPENSE_DETAIL)
                    .set(INCOME_EXPENSE_DETAIL.DETAIL_TYPE, newDetailType)
                    .where(
                            INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(INCOME_EXPENSE_DETAIL.TYPE.eq(data.getIncomeExpenseType()))
                                    .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(oldDetailType))
                    )
                    .execute();

            // Since there is no select().as() transformation.
            // Then we need to convert the snake case database column name to camel case to client.
            savedDetailType = detailType.intoMap()
                    .entrySet()
                    .stream()
                    .collect(Collectors.toMap(
                            entry -> SharedUtil.convertSnakeToCamel(entry.getKey()),
                            Map.Entry::getValue
                    ));
        }

        return savedDetailType;
    }

    /**
     * Deletes the IncomeExpenseDetailTypes with provides list of id.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param name              : The string of the detail type name.
     * @param accountGroupId    : The string of the user account group id.
     * @return True means deleted. False means this detail type has been used and cannot be deleted.
     */
    public boolean delete(String incomeExpenseType, String name, String accountGroupId) {
        boolean isDeleted = false;

        // Checks if this detail type is used by income/expense.
        if (dslContextWrapper.count(INCOME_EXPENSE_DETAIL, INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                .and(INCOME_EXPENSE_DETAIL.TYPE.eq(incomeExpenseType))
                .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(name))) == 0) {
            // If this detail type is not used then deletes it.
            dslContext.deleteFrom(INCOME_EXPENSE_DETAIL_TYPE)
                    .where(
                            INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                                    .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.eq(name))
                    )
                    .execute();

            isDeleted = true;
        }

        return isDeleted;
    }

    /**
     * Deletes the IncomeExpenseDetailTypes with provides list of name.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param accountGroupId    : The string of the user account group id.
     * @param names             : The string list of name.
     * @return The list of the id string which have been actually deleted.
     */
    public List<String> delete(String incomeExpenseType, List<String> names, String accountGroupId) {
        List<String> deletedDetailTypeIds = new ArrayList<String>();

        // Queries the input id list and makes sure they belong to the specific account group.
        // This is an example shows how to secure the data by taking extra action.
        // This can void other account group users call api directly and provide these ids which they don't actually own them.
        names = queryAccountGroupDetailTypes(incomeExpenseType, names, accountGroupId);

        // Queries all detail type names which used by income/expense.
        List<String> usedNames = queryUsedDetailTypeNames(incomeExpenseType, names, accountGroupId);

        // Excludes all used detail type names.
        usedNames.forEach(names::remove);

        if (!names.isEmpty()) {
            // Queries all ids based on incomeExpenseType and names.
            // This will be returned to client for list item managing.
            deletedDetailTypeIds = dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
                    .where(
                            INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                                    .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.in(names))
                    )
                    .fetch(INCOME_EXPENSE_DETAIL_TYPE.ID);

            dslContext.deleteFrom(INCOME_EXPENSE_DETAIL_TYPE)
                    .where(INCOME_EXPENSE_DETAIL_TYPE.ID.in(deletedDetailTypeIds))
                    .execute();
        }

        return deletedDetailTypeIds;
    }

    /**
     * Queries which detail type is used by income/expense based on provided income/expense type and detail type name list.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param detailTypeNames:  The string list of detail type names.
     * @param accountGroupId:   The string of the user's account group id.
     * @return The string list of detail type name which is used by income/expense. Any unused detail type will be filtered.
     */
    private List<String> queryUsedDetailTypeNames(String incomeExpenseType, List<String> detailTypeNames, String accountGroupId) {
        if (!detailTypeNames.isEmpty()) {
            return dslContext.selectDistinct(INCOME_EXPENSE_DETAIL.DETAIL_TYPE)
                    .from(INCOME_EXPENSE_DETAIL)
                    .where(
                            INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(INCOME_EXPENSE_DETAIL.TYPE.eq(incomeExpenseType))
                                    .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.in(detailTypeNames))
                    )
                    .fetchInto(String.class);
        } else {
            return new ArrayList<>();
        }
    }
}
